using Infrastructure;
using Infrastructure.Attribute;
using System.Text;
using ZR.Common;
using ZR.Model;
using ZR.Model.System;
using ZR.Model.System.Dto;
using ZR.Repository;
using ZR.ServiceCore.Services.IService;

namespace ZR.ServiceCore.Services
{
    /// <summary>
    /// 风力数据PPI查询服务实现
    /// </summary>
    [AppService(ServiceType = typeof(IWindDataPPIService), ServiceLifetime = LifeTime.Transient)]
    public class WindDataPPIService : BaseService<WindDataPPI>, IWindDataPPIService
    {
        private static readonly object _lockObject = new object();
        private static List<string> _cachedTableNames = null;
        private static DateTime _lastCacheTime = DateTime.MinValue;
        private static readonly TimeSpan _cacheExpiration = TimeSpan.FromMinutes(5); // 缓存5分钟
        /// <summary>
        /// 获取所有PPI表名列表（包含Z_RADR_I但不包含LR_096的表）
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllPPITableNames()
        {
            lock (_lockObject)
            {
                // 检查缓存是否有效
                if (_cachedTableNames != null && DateTime.Now - _lastCacheTime < _cacheExpiration)
                {
                    return _cachedTableNames;
                }

            try
            {
                // 先查询所有包含Z_RADR_I的表
                var allTablesSql = @"
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE' 
                    AND TABLE_NAME LIKE '%_P_CDWL_%'";
                
                var allTables = Context.Ado.SqlQuery<string>(allTablesSql).ToList();
                
                // 过滤出不包含LR_096的表
                var ppiTables = allTables.Where(table => !table.Contains("LR_096")).ToList();
                    
                    // 更新缓存
                    _cachedTableNames = ppiTables;
                    _lastCacheTime = DateTime.Now;
                
                return ppiTables;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取PPI表名列表时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                    return _cachedTableNames ?? new List<string>();
                }
            }
        }

        /// <summary>
        /// 根据时间参数获取对应的PPI表名列表
        /// </summary>
        /// <param name="timeParam">时间参数</param>
        /// <returns></returns>
        public List<string> GetPPITableNames(string timeParam)
        {
            try
            {
                var allTables = GetAllPPITableNames();
                
                if (string.IsNullOrEmpty(timeParam))
                {
                    return allTables;
                }

                // 根据时间参数过滤表名
                return allTables.Where(table => table.Contains(timeParam)).ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"根据时间参数获取PPI表名时出错: {ex.Message}");
                return new List<string>();
            }
        }

        /// <summary>
        /// 获取风力数据PPI查询列表（带缓存）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<WindDataPPI> SelectWindDataPPIList(WindDataPPIQueryDto query, PagerInfo pager)
        {
            // 生成缓存键（基于查询条件和分页参数）
            var cacheKey = BuildWindDataPPICacheKey(query, pager);
            
            // 尝试从缓存获取结果
            var cachedResult = CacheHelper.GetCache<PagedInfo<WindDataPPI>>(cacheKey);
            if (cachedResult != null)
            {
                Console.WriteLine($"风力数据PPI查询 - 从缓存获取数据，缓存键: {cacheKey}");
                return cachedResult;
            }

            // 缓存未命中，执行数据库查询
            // 根据TimeParam获取包含该时间参数的表名列表
            var sourceTables = GetPPITableNames(query.TimeParam);

            if (!sourceTables.Any())
            {
                var emptyResult = new PagedInfo<WindDataPPI>
                {
                    Result = new List<WindDataPPI>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
                // 空结果也缓存，避免重复查询
                CacheHelper.SetCache(cacheKey, emptyResult, 10);
                return emptyResult;
            }

            PagedInfo<WindDataPPI> result;

            // 如果指定了特定表，只查询该表
            if (!string.IsNullOrEmpty(query.SourceTable))
            {
                if (sourceTables.Contains(query.SourceTable))
                {
                    result = GetDataFromTableWithPaging(query.SourceTable, query, pager);
                }
                else
                {
                    result = new PagedInfo<WindDataPPI>
                    {
                        Result = new List<WindDataPPI>(),
                        TotalNum = 0,
                        PageIndex = pager.PageNum,
                        PageSize = pager.PageSize
                    };
                }
            }
            else
            {
                // 多表查询优化：使用UNION ALL合并查询，在数据库层面进行分页
                result = GetDataFromMultipleTablesWithPaging(sourceTables, query, pager);
            }

            // 将结果存入缓存（缓存10分钟）
            CacheHelper.SetCache(cacheKey, result, 10);
            Console.WriteLine($"风力数据PPI查询 - 查询数据库并缓存结果，缓存键: {cacheKey}");

            return result;
        }

        /// <summary>
        /// 构建风力数据PPI查询缓存键（基于查询条件和分页参数）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private string BuildWindDataPPICacheKey(WindDataPPIQueryDto query, PagerInfo pager)
        {
            var keyBuilder = new StringBuilder("WindDataPPI:List:");
            
            // 主键ID
            if (query.Id.HasValue)
            {
                keyBuilder.Append($"Id:{query.Id.Value}:");
            }
            
            // 数据来源表名
            keyBuilder.Append($"SourceTable:{query.SourceTable ?? "All"}:");
            
            // X坐标
            keyBuilder.Append($"XCoordinate:{query.XCoordinate ?? "All"}:");
            
            // Y坐标
            keyBuilder.Append($"YCoordinate:{query.YCoordinate ?? "All"}:");
            
            // 风速
            keyBuilder.Append($"WindSpeed:{query.WindSpeed ?? "All"}:");
            
            // 风向
            keyBuilder.Append($"WindDirection:{query.WindDirection ?? "All"}:");
            
            // 开始时间
            if (query.BeginTime.HasValue)
            {
                keyBuilder.Append($"BeginTime:{query.BeginTime.Value:yyyyMMddHHmmss}:");
            }
            
            // 结束时间
            if (query.EndTime.HasValue)
            {
                keyBuilder.Append($"EndTime:{query.EndTime.Value:yyyyMMddHHmmss}:");
            }
            
            // 时间参数
            keyBuilder.Append($"TimeParam:{query.TimeParam ?? "All"}:");
            
            // 最大风速
            if (query.MaxWindSpeed.HasValue)
            {
                keyBuilder.Append($"MaxWindSpeed:{query.MaxWindSpeed.Value}:");
            }
            
            // 最小风速
            if (query.MinWindSpeed.HasValue)
            {
                keyBuilder.Append($"MinWindSpeed:{query.MinWindSpeed.Value}:");
            }
            
            // 风速模糊查询
            keyBuilder.Append($"WindSpeedLike:{query.WindSpeedLike ?? "All"}:");
            
            // X坐标范围
            if (query.XCoordinateMin.HasValue)
            {
                keyBuilder.Append($"XCoordinateMin:{query.XCoordinateMin.Value}:");
            }
            if (query.XCoordinateMax.HasValue)
            {
                keyBuilder.Append($"XCoordinateMax:{query.XCoordinateMax.Value}:");
            }
            
            // Y坐标范围
            if (query.YCoordinateMin.HasValue)
            {
                keyBuilder.Append($"YCoordinateMin:{query.YCoordinateMin.Value}:");
            }
            if (query.YCoordinateMax.HasValue)
            {
                keyBuilder.Append($"YCoordinateMax:{query.YCoordinateMax.Value}:");
            }
            
            // 城市参数
            keyBuilder.Append($"City:{query.City ?? "All"}:");
            
            // 分页参数
            keyBuilder.Append($"PageNum:{pager.PageNum}:PageSize:{pager.PageSize}");
            
            return keyBuilder.ToString();
        }

        /// <summary>
        /// 从指定表获取数据（带分页）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private PagedInfo<WindDataPPI> GetDataFromTableWithPaging(string tableName, WindDataPPIQueryDto query, PagerInfo pager)
        {
            try
            {
                // 构建查询SQL，包含分页和风速范围过滤
                var sql = BuildSelectSqlWithPaging(tableName, query, pager);
                Console.WriteLine($"PPI查询 - 执行SQL: {sql}");
                
                // 构建参数对象
                var parameters = new
                {
                    id = query.Id,
                    xCoordinate = query.XCoordinate,
                    yCoordinate = query.YCoordinate,
                    windSpeed = query.WindSpeed,
                    windDirection = query.WindDirection,
                    maxWindSpeed = query.MaxWindSpeed,
                    minWindSpeed = query.MinWindSpeed,
                    offset = (pager.PageNum - 1) * pager.PageSize,
                    limit = pager.PageSize
                };
                
                // 查询数据
                var result = Context.Ado.SqlQuery<WindData>(sql, parameters);
                
                // 查询总数
                var countSql = BuildCountSql(tableName, query);
                var totalCount = Context.Ado.SqlQuery<int>(countSql, new
                {
                    id = query.Id,
                    xCoordinate = query.XCoordinate,
                    yCoordinate = query.YCoordinate,
                    windSpeed = query.WindSpeed,
                    windDirection = query.WindDirection,
                    maxWindSpeed = query.MaxWindSpeed,
                    minWindSpeed = query.MinWindSpeed
                }).FirstOrDefault();
                
                Console.WriteLine($"PPI查询 - SQL查询返回行数: {result.Count}, 总数: {totalCount}");
                
                // 将WindData转换为WindDataPPI
                var ppiList = result.Select(windData => new WindDataPPI
                {
                    Id = windData.Id,
                    SourceTable = tableName,
                    Time = windData.Time,
                    XCoordinate = windData.XCoordinate,
                    YCoordinate = windData.YCoordinate,
                    WindSpeed = windData.WindSpeed,
                    WindDirection = windData.WindDirection
                }).ToList();

                return new PagedInfo<WindDataPPI>
                {
                    Result = ppiList,
                    TotalNum = totalCount,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询表 {tableName} 时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new PagedInfo<WindDataPPI>
                {
                    Result = new List<WindDataPPI>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
        }

        /// <summary>
        /// 从多个表获取数据（带分页）
        /// </summary>
        /// <param name="tableNames">表名列表</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private PagedInfo<WindDataPPI> GetDataFromMultipleTablesWithPaging(List<string> tableNames, WindDataPPIQueryDto query, PagerInfo pager)
        {
            try
            {
                // 限制查询的表数量，避免SQL过长
                var limitedTables = tableNames.Take(5).ToList(); // 减少到5个表
                
                var allData = new List<WindDataPPI>();
                var totalCount = 0;
                
                // 分别查询每个表，然后合并结果
                foreach (var tableName in limitedTables)
                {
                    try
                    {
                        // 查询单个表的数据
                        var tableData = GetDataFromTableWithPaging(tableName, query, new PagerInfo 
                        { 
                            PageNum = 1, 
                            PageSize = pager.PageSize * 2 // 获取更多数据用于排序
                        });
                        
                        if (tableData.Result != null)
                        {
                            allData.AddRange(tableData.Result);
                            totalCount += tableData.TotalNum;
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"查询表 {tableName} 时出错: {ex.Message}");
                    }
                }
                
                // 排序
                allData = allData.OrderBy(x => x.SourceTable).ThenBy(x => x.Id).ToList();
                
                // 分页
                var pagedData = allData.Skip((pager.PageNum - 1) * pager.PageSize).Take(pager.PageSize).ToList();
                
                Console.WriteLine($"PPI多表查询 - 返回行数: {pagedData.Count}, 总数: {totalCount}");
                
                return new PagedInfo<WindDataPPI>
                {
                    Result = pagedData,
                    TotalNum = totalCount,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"多表查询时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new PagedInfo<WindDataPPI>
                {
                    Result = new List<WindDataPPI>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
        }

        /// <summary>
        /// 从指定表获取数据（旧方法，保留兼容性）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<WindDataPPI> GetDataFromTable(string tableName, WindDataPPIQueryDto query)
        {
            try
            {
                // 构建查询SQL
                var sql = BuildSelectSql(tableName, query);
                Console.WriteLine($"PPI查询 - 执行SQL: {sql}");
                
                // 构建参数对象
                var parameters = new
                {
                    id = query.Id,
                    xCoordinate = query.XCoordinate,
                    yCoordinate = query.YCoordinate,
                    windSpeed = query.WindSpeed,
                    windDirection = query.WindDirection
                };
                
                // 使用构建的SQL和参数直接查询
                var result = Context.Ado.SqlQuery<WindData>(sql, parameters);
                
                Console.WriteLine($"PPI查询 - SQL查询返回行数: {result.Count}");
                
                // 将WindData转换为WindDataPPI
                var ppiList = result.Select(windData => new WindDataPPI
                {
                    Id = windData.Id,
                    SourceTable = tableName,
                    Time= windData.Time,
                    XCoordinate = windData.XCoordinate,
                    YCoordinate = windData.YCoordinate,
                    WindSpeed = windData.WindSpeed,
                    WindDirection = windData.WindDirection
                }).ToList();
                return ppiList;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询表 {tableName} 时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new List<WindDataPPI>();
            }
        }

        /// <summary>
        /// 构建带分页的查询SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private string BuildSelectSqlWithPaging(string tableName, WindDataPPIQueryDto query, PagerInfo pager)
        {
            var selectFields = "id, x_coordinate, y_coordinate, wind_speed, wind_direction, time";
            var whereConditions = BuildWhereConditions(query);

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT {selectFields} FROM {tableName} {whereClause} ORDER BY id LIMIT @limit OFFSET @offset";
        }

        /// <summary>
        /// 构建用于UNION的查询SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private string BuildSelectSqlForUnion(string tableName, WindDataPPIQueryDto query)
        {
            var selectFields = $"id, x_coordinate, y_coordinate, wind_speed, wind_direction, time, '{tableName}' as source_table";
            var whereConditions = BuildWhereConditions(query);

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT {selectFields} FROM {tableName} {whereClause}";
        }

        /// <summary>
        /// 构建计数SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private string BuildCountSql(string tableName, WindDataPPIQueryDto query)
        {
            var whereConditions = BuildWhereConditions(query);
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT COUNT(*) FROM {tableName} {whereClause}";
        }

        /// <summary>
        /// 构建WHERE条件
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<string> BuildWhereConditions(WindDataPPIQueryDto query)
        {
            var whereConditions = new List<string>();

            // 注意：表名过滤在调用层面处理，这里不处理

            // 添加其他过滤条件（使用参数化查询避免SQL注入）
            if (query.Id.HasValue)
            {
                whereConditions.Add($"id = @id");
            }

            if (!string.IsNullOrEmpty(query.XCoordinate))
            {
                whereConditions.Add($"x_coordinate = @xCoordinate");
            }

            if (!string.IsNullOrEmpty(query.YCoordinate))
            {
                whereConditions.Add($"y_coordinate = @yCoordinate");
            }

            if (!string.IsNullOrEmpty(query.WindSpeed))
            {
                whereConditions.Add($"wind_speed = @windSpeed");
            }

            if (!string.IsNullOrEmpty(query.WindDirection))
            {
                whereConditions.Add($"wind_direction = @windDirection");
            }

            // 添加风速范围过滤（用于报警记录）
            if (query.MaxWindSpeed.HasValue || query.MinWindSpeed.HasValue)
            {
                var windSpeedConditions = new List<string>();
                
                if (query.MaxWindSpeed.HasValue)
                {
                    windSpeedConditions.Add($"(CAST(wind_speed AS DECIMAL(10,2)) > {query.MaxWindSpeed.Value})");
                }
                
                if (query.MinWindSpeed.HasValue)
                {
                    windSpeedConditions.Add($"(CAST(wind_speed AS DECIMAL(10,2)) < {query.MinWindSpeed.Value})");
                }
                
                if (windSpeedConditions.Any())
                {
                    whereConditions.Add($"({string.Join(" OR ", windSpeedConditions)})");
                }
            }

            // 添加风速模糊查询（用于风速包含特定值的查询）
            if (!string.IsNullOrEmpty(query.WindSpeedLike))
            {
                whereConditions.Add($"wind_speed LIKE '%{query.WindSpeedLike}%'");
            }

            // 添加X坐标范围过滤
            if (query.XCoordinateMin.HasValue || query.XCoordinateMax.HasValue)
            {
                var xCoordinateConditions = new List<string>();
                
                if (query.XCoordinateMin.HasValue)
                {
                    xCoordinateConditions.Add($"CAST(x_coordinate AS DECIMAL(10,6)) >= {query.XCoordinateMin.Value}");
                }
                
                if (query.XCoordinateMax.HasValue)
                {
                    xCoordinateConditions.Add($"CAST(x_coordinate AS DECIMAL(10,6)) <= {query.XCoordinateMax.Value}");
                }
                
                if (xCoordinateConditions.Any())
                {
                    whereConditions.Add($"({string.Join(" AND ", xCoordinateConditions)})");
                }
            }

            // 添加Y坐标范围过滤
            if (query.YCoordinateMin.HasValue || query.YCoordinateMax.HasValue)
            {
                var yCoordinateConditions = new List<string>();
                
                if (query.YCoordinateMin.HasValue)
                {
                    yCoordinateConditions.Add($"CAST(y_coordinate AS DECIMAL(10,6)) >= {query.YCoordinateMin.Value}");
                }
                
                if (query.YCoordinateMax.HasValue)
                {
                    yCoordinateConditions.Add($"CAST(y_coordinate AS DECIMAL(10,6)) <= {query.YCoordinateMax.Value}");
                }
                
                if (yCoordinateConditions.Any())
                {
                    whereConditions.Add($"({string.Join(" AND ", yCoordinateConditions)})");
                }
            }

            // 添加数据有效性筛选条件
            whereConditions.Add("x_coordinate != '-9999.00'");
            whereConditions.Add("y_coordinate != '-9999.00'");
            whereConditions.Add("wind_speed != '-9999.00'");
            whereConditions.Add("wind_direction != '-9999.00'");

            return whereConditions;
        }

        /// <summary>
        /// 构建查询SQL（旧方法，保留兼容性）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private string BuildSelectSql(string tableName, WindDataPPIQueryDto query)
        {
            // 尝试不同的字段名组合，以处理可能的字段名不匹配问题
            var selectFields = "id, x_coordinate, y_coordinate, wind_speed, wind_direction,time";
            var whereConditions = new List<string>();

            // 添加表名过滤
            if (!string.IsNullOrEmpty(query.SourceTable) && query.SourceTable != tableName)
            {
                return "SELECT 1 WHERE 1=0"; // 返回空结果
            }

            // 添加其他过滤条件（使用参数化查询避免SQL注入）
            if (query.Id.HasValue)
            {
                whereConditions.Add($"id = @id");
            }

            if (!string.IsNullOrEmpty(query.XCoordinate))
            {
                whereConditions.Add($"x_coordinate = @xCoordinate");
            }

            if (!string.IsNullOrEmpty(query.YCoordinate))
            {
                whereConditions.Add($"y_coordinate = @yCoordinate");
            }

            if (!string.IsNullOrEmpty(query.WindSpeed))
            {
                whereConditions.Add($"wind_speed = @windSpeed");
            }

            if (!string.IsNullOrEmpty(query.WindDirection))
            {
                whereConditions.Add($"wind_direction = @windDirection");
            }

            // 添加数据有效性筛选条件
            whereConditions.Add("x_coordinate != '-9999.00'");
            whereConditions.Add("y_coordinate != '-9999.00'");
            whereConditions.Add("wind_speed != '-9999.00'");
            whereConditions.Add("wind_direction != '-9999.00'");

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT {selectFields} FROM {tableName} {whereClause} LIMIT 1000";
        }

        /// <summary>
        /// 从字典中获取值
        /// </summary>
        /// <typeparam name="T">值类型</typeparam>
        /// <param name="dict">字典</param>
        /// <param name="key">键</param>
        /// <returns></returns>
        private T GetValue<T>(Dictionary<string, object> dict, string key)
        {
            if (dict == null)
            {
                Console.WriteLine($"GetValue - 字典为null，键: {key}");
                return default(T);
            }

            // 首先尝试精确匹配
            if (dict.ContainsKey(key))
            {
                return GetValueByKey<T>(dict, key);
            }

            // 如果精确匹配失败，尝试大小写不敏感匹配
            var caseInsensitiveKey = dict.Keys.FirstOrDefault(k => 
                string.Equals(k, key, StringComparison.OrdinalIgnoreCase));
            
            if (caseInsensitiveKey != null)
            {
                Console.WriteLine($"GetValue - 使用大小写不敏感匹配，原键: {key}, 匹配键: {caseInsensitiveKey}");
                return GetValueByKey<T>(dict, caseInsensitiveKey);
            }

            Console.WriteLine($"GetValue - 字典中不包含键: {key}，可用键: {string.Join(", ", dict.Keys)}");
            return default(T);
        }

        private T GetValueByKey<T>(Dictionary<string, object> dict, string key)
        {
            if (dict[key] == null)
            {
                Console.WriteLine($"GetValue - 键 {key} 的值为null");
                return default(T);
            }

            try
            {
                var value = dict[key];
                Console.WriteLine($"GetValue - 键: {key}, 值: {value}, 值类型: {value.GetType()}, 目标类型: {typeof(T)}");
                
                if (value is T)
                {
                    return (T)value;
                }

                // 特殊处理可空类型
                if (typeof(T).IsGenericType && typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    var underlyingType = Nullable.GetUnderlyingType(typeof(T));
                    if (underlyingType != null)
                    {
                        return (T)Convert.ChangeType(value, underlyingType);
                    }
                }

                return (T)Convert.ChangeType(value, typeof(T));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"GetValue - 转换失败，键: {key}, 值: {dict[key]}, 错误: {ex.Message}");
                return default(T);
            }
        }

        /// <summary>
        /// 应用过滤条件
        /// </summary>
        /// <param name="data">数据列表</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<WindDataPPI> ApplyFilters(List<WindDataPPI> data, WindDataPPIQueryDto query)
        {
            var filteredData = data.AsQueryable();

            if (!string.IsNullOrEmpty(query.SourceTable))
            {
                filteredData = filteredData.Where(x => x.SourceTable == query.SourceTable);
            }

            if (!string.IsNullOrEmpty(query.XCoordinate))
            {
                filteredData = filteredData.Where(x => x.XCoordinate == query.XCoordinate);
            }

            if (!string.IsNullOrEmpty(query.YCoordinate))
            {
                filteredData = filteredData.Where(x => x.YCoordinate == query.YCoordinate);
            }

            if (!string.IsNullOrEmpty(query.WindSpeed))
            {
                filteredData = filteredData.Where(x => x.WindSpeed == query.WindSpeed);
            }

            if (!string.IsNullOrEmpty(query.WindDirection))
            {
                filteredData = filteredData.Where(x => x.WindDirection == query.WindDirection);
            }

            return filteredData.ToList();
        }

        /// <summary>
        /// 模糊查询风力数据PPI查询列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<WindDataPPI> SelectWindDataPPIListWithLike(WindDataPPIQueryDto query, PagerInfo pager)
        {
            return SelectWindDataPPIList(query, pager);
        }

        /// <summary>
        /// 获取风力数据PPI查询统计信息
        /// </summary>
        /// <returns></returns>
        public object GetWindDataPPIStatistics()
        {
            var sourceTables = GetAllPPITableNames();
            var statistics = new Dictionary<string, object>();

            foreach (var tableName in sourceTables)
            {
                try
                {
                    var countSql = $"SELECT COUNT(*) as total FROM {tableName}";
                    var result = Context.Ado.SqlQuery<Dictionary<string, object>>(countSql);
                    var count = GetValue<long>(result.FirstOrDefault(), "total");
                    statistics[tableName] = count;
                }
                catch (Exception ex)
                {
                    statistics[tableName] = $"错误: {ex.Message}";
                }
            }

            return new
            {
                SourceTables = sourceTables,
                TableStatistics = statistics,
                TotalTables = sourceTables.Count
            };
        }

        /// <summary>
        /// 清理表名缓存
        /// </summary>
        public static void ClearTableNameCache()
        {
            lock (_lockObject)
            {
                _cachedTableNames = null;
                _lastCacheTime = DateTime.MinValue;
            }
        }

        /// <summary>
        /// 获取风力数据PPI历史数据列表（包含统计信息）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<WindDataPPI> SelectWindDataPPIHistoryList(WindDataPPIQueryDto query, PagerInfo pager)
        {
            // 先获取分页数据
            var result = SelectWindDataPPIList(query, pager);
            
            if (result == null)
            {
                result = new PagedInfo<WindDataPPI>
                {
                    Result = new List<WindDataPPI>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
            
            // 使用SQL聚合函数计算统计信息（性能优化）
            var statistics = CalculateWindSpeedStatistics(query);
            
            // 将统计信息添加到 Extra 字典中
            result.Extra = new Dictionary<string, object>
            {
                { "maxWindSpeed", statistics.MaxWindSpeed },
                { "minWindSpeed", statistics.MinWindSpeed },
                { "avgWindSpeed", statistics.AvgWindSpeed },
                { "mainWindDirection", statistics.MainWindDirection }
            };

            return result;
        }

        /// <summary>
        /// 计算风速统计信息（使用SQL聚合函数，性能优化）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private (decimal? MaxWindSpeed, decimal? MinWindSpeed, decimal? AvgWindSpeed, string MainWindDirection) CalculateWindSpeedStatistics(WindDataPPIQueryDto query)
        {
            try
            {
                // 根据TimeParam获取包含该时间参数的表名列表
                var sourceTables = GetPPITableNames(query.TimeParam);

                if (!sourceTables.Any())
                {
                    return (null, null, null, null);
                }

                decimal? maxWindSpeed = null;
                decimal? minWindSpeed = null;
                decimal? avgWindSpeed = null;
                string mainWindDirection = null;

                // 如果指定了特定表，只查询该表
                if (!string.IsNullOrEmpty(query.SourceTable) && sourceTables.Contains(query.SourceTable))
                {
                    var stats = CalculateStatisticsFromTable(query.SourceTable, query);
                    maxWindSpeed = stats.MaxWindSpeed;
                    minWindSpeed = stats.MinWindSpeed;
                    avgWindSpeed = stats.AvgWindSpeed;
                    mainWindDirection = stats.MainWindDirection;
                }
                else
                {
                    // 多表查询：分别计算每个表的统计信息，然后取整体最大最小值，计算加权平均值
                    var allMaxValues = new List<decimal>();
                    var allMinValues = new List<decimal>();
                    var totalSum = 0m;
                    var totalCount = 0;
                    var directionCounts = new Dictionary<string, int>();

                    // 限制查询的表数量，避免性能问题（可以根据实际情况调整）
                    var limitedTables = sourceTables.Take(20).ToList();

                    foreach (var tableName in limitedTables)
                    {
                        try
                        {
                            var stats = CalculateStatisticsFromTable(tableName, query);
                            if (stats.MaxWindSpeed.HasValue)
                            {
                                allMaxValues.Add(stats.MaxWindSpeed.Value);
                            }
                            if (stats.MinWindSpeed.HasValue)
                            {
                                allMinValues.Add(stats.MinWindSpeed.Value);
                            }
                            // 计算加权平均：sum(avg * count) / sum(count)
                            if (stats.AvgWindSpeed.HasValue && stats.Count > 0)
                            {
                                totalSum += stats.AvgWindSpeed.Value * stats.Count;
                                totalCount += stats.Count;
                            }
                            // 统计主要风向：累加每个表的方向计数
                            if (!string.IsNullOrEmpty(stats.MainWindDirection))
                            {
                                if (directionCounts.ContainsKey(stats.MainWindDirection))
                                {
                                    directionCounts[stats.MainWindDirection] += stats.Count;
                                }
                                else
                                {
                                    directionCounts[stats.MainWindDirection] = stats.Count;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"计算表 {tableName} 统计信息时出错: {ex.Message}");
                        }
                    }

                    // 计算整体统计
                    maxWindSpeed = allMaxValues.Any() ? allMaxValues.Max() : null;
                    minWindSpeed = allMinValues.Any() ? allMinValues.Min() : null;
                    avgWindSpeed = totalCount > 0 ? totalSum / totalCount : null;
                    // 获取占比最多的方向
                    mainWindDirection = directionCounts.Any() 
                        ? directionCounts.OrderByDescending(kv => kv.Value).First().Key 
                        : null;
                }

                return (maxWindSpeed, minWindSpeed, avgWindSpeed, mainWindDirection);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"计算风速统计信息时出错: {ex.Message}");
                return (null, null, null, null);
            }
        }

        /// <summary>
        /// 统计结果类
        /// </summary>
        private class StatisticsResult
        {
            public decimal? MaxWindSpeed { get; set; }
            public decimal? MinWindSpeed { get; set; }
            public decimal? AvgWindSpeed { get; set; }
            public int Count { get; set; }
            public string MainWindDirection { get; set; }
        }

        /// <summary>
        /// 从指定表计算统计信息（直接执行SQL聚合查询，性能优化：优先过滤无效值）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private (decimal? MaxWindSpeed, decimal? MinWindSpeed, decimal? AvgWindSpeed, int Count, string MainWindDirection) CalculateStatisticsFromTable(string tableName, WindDataPPIQueryDto query)
        {
            try
            {
                // 性能优化：优先过滤无效值，减少聚合函数需要处理的数据量
                // 将最有效的过滤条件放在最前面，让数据库引擎可以更早地过滤数据
                var baseFilters = new List<string>
                {
                    "wind_speed != '-9999.00'",  // 优先过滤无效值（最常用，放在最前面）
                    "wind_speed IS NOT NULL",
                    "wind_speed != ''",
                    "CAST(wind_speed AS DECIMAL(10,2)) IS NOT NULL"  // 确保可以转换为数字
                };

                // 添加其他查询条件（BuildWhereConditions已经包含了wind_speed != '-9999.00'，但我们会去重）
                var whereConditions = BuildWhereConditions(query);
                
                // 合并条件：基础过滤条件在前，其他条件在后，并去重
                var allConditions = baseFilters.Concat(whereConditions)
                    .Where(c => !string.IsNullOrWhiteSpace(c))
                    .Distinct()
                    .ToList();

                var whereClause = allConditions.Count > 0 ? "WHERE " + string.Join(" AND ", allConditions) : "";

                // 添加风向有效性过滤
                var windDirectionFilters = new List<string>
                {
                    "wind_direction != '-9999.00'",
                    "wind_direction IS NOT NULL",
                    "wind_direction != ''",
                    "CAST(wind_direction AS DECIMAL(10,2)) IS NOT NULL"
                };
                var allDirectionConditions = allConditions.Concat(windDirectionFilters).ToList();
                var directionWhereClause = allDirectionConditions.Count > 0 ? "WHERE " + string.Join(" AND ", allDirectionConditions) : "";

                // 直接执行SQL聚合查询
                // 由于WHERE条件已经过滤了无效值，聚合函数只需要处理有效数据
                // 同时计算主要风向（占比最多的方向）
                // 使用 ((angle % 360) + 360) % 360 归一化角度
                // 在MySQL中，MOD函数处理负数：MOD(-10, 360) = 350，所以需要 MOD(MOD(angle, 360) + 360, 360)
                var sql = $@"
                    SELECT 
                        MAX(CAST(wind_speed AS DECIMAL(10,2))) as MaxWindSpeed,
                        MIN(CAST(wind_speed AS DECIMAL(10,2))) as MinWindSpeed,
                        AVG(CAST(wind_speed AS DECIMAL(10,2))) as AvgWindSpeed,
                        COUNT(*) as Count,
                        (
                            SELECT direction_name
                            FROM (
                                SELECT 
                                    CASE 
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 337.5 
                                             OR MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 22.5 THEN '北'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 22.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 67.5 THEN '东北'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 67.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 112.5 THEN '东'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 112.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 157.5 THEN '东南'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 157.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 202.5 THEN '南'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 202.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 247.5 THEN '西南'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 247.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 292.5 THEN '西'
                                        WHEN MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) >= 292.5 
                                             AND MOD(MOD(CAST(wind_direction AS DECIMAL(10,2)), 360) + 360, 360) < 337.5 THEN '西北'
                                        ELSE '未知'
                                    END as direction_name,
                                    COUNT(*) as direction_count
                                FROM {tableName} 
                                {directionWhereClause}
                                GROUP BY direction_name
                                ORDER BY direction_count DESC
                                LIMIT 1
                            ) as direction_stats
                        ) as MainWindDirection
                    FROM {tableName} 
                    {whereClause}";

                Console.WriteLine($"统计查询SQL: {sql}");

                // 直接使用SqlQuery返回强类型结果
                var result = Context.Ado.SqlQuery<StatisticsResult>(sql).FirstOrDefault();

                if (result == null)
                {
                    Console.WriteLine($"表 {tableName} 统计查询返回 null，可能没有符合条件的数据");
                    return (null, null, null, 0, null);
                }

                Console.WriteLine($"表 {tableName} 统计结果 - Max: {result.MaxWindSpeed}, Min: {result.MinWindSpeed}, Avg: {result.AvgWindSpeed}, Count: {result.Count}, MainDirection: {result.MainWindDirection}");

                return (result.MaxWindSpeed, result.MinWindSpeed, result.AvgWindSpeed, result.Count, result.MainWindDirection);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"从表 {tableName} 计算统计信息时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return (null, null, null, 0, null);
            }
        }
    }
}